package edu.weber.java.database;

import java.sql.*;

public class SQLDemo {
	private String message;
	
	public String Execute(String[] args) throws SQLException{
		Connection c = null;
		try{
			//first step: register the JDBC Driver - Method 1
			Class.forName("com.mysql.jdbc.Driver");
			
			//Register the JDBC Driver - Method 2
			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			System.out.println("Driver is loaded");
			
			//establish connection to the DB
			c = DriverManager.getConnection("jdbc:mysql://icarus.cs.weber.edu:3306/W01085371","W01085371","Brandoncs!");//:localhost:3306/W01085371","W01085371","85371cs!");
			System.out.println("Connection Created");
			
			//Execute SQL
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery("Select * From authors");
			System.out.println("ID\tfirstName\tLastName");
			
			message="";
			while(rs.next())
			{
				message+=(rs.getString(1)+"\t"+rs.getString(2)+"\t\t"+rs.getString(3));
			}
						
			rs.close();
			s.close();
			message = "smile.";
			return message;
		}
		catch(Exception e){
			return "Exception: " + e;
		}
		finally{
			c.close();
		}
	}
	
	
	
	/*public static void main(String[] args) throws SQLException{
		Connection c = null;
		try{
			//first step: register the JDBC Driver - Method 1
			Class.forName("com.mysql.jdbc.Driver");
			
			//Register the JDBC Driver - Method 2
			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			System.out.println("Driver is loaded");
			
			//establish connection to the DB
			c = DriverManager.getConnection("jdbc:mysql://icarus.cs.weber.edu:3306/W01085371","W01085371","Brandoncs!");//:localhost:3306/W01085371","W01085371","85371cs!");
			System.out.println("Connection Created");
			
			//Execute SQL
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery("Select * From authors");
			System.out.println("ID\tfirstName\tLastName");
			
			while(rs.next())
			{
				System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t\t"+rs.getString(3));
			}
			
			//Inserting data
			String str ="insert into authors(authorID,firstName,lastName) values (5,'Daniel','Cohen')";
			s.executeQuery(str);
			rs = s.executeQuery("select * from authors");
			System.out.println("ID\t\firstName\tlastName");
			
			//Retrieve data from ResultSet
			while(rs.next())
			{
				System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t\t"+rs.getString(3));
			}
			
			//Prepared Statement in JDBC
			String str2 ="insert into authors (authorID,firstName,lastName) values (?,?,?)";
			PreparedStatement ps = c.prepareStatement(str2);
			ps.setInt(1,6);
			ps.setString(2, "Brandon");
			ps.setString(3, "Campbell");
			ps.execute();
			ps.clearParameters();
			ps.setInt(1, 7);
			ps.setString(2, "Don");
			ps.setString(3,"Julio");
			ps.execute();
			ps.clearParameters();
			System.out.println("Records Inserted");
			 //while statement
			
			rs.close();
			s.close();
		}
		catch(Exception e){
			System.out.println("Exception: " + e);
		}
		finally{
			c.close();
		}
	}*/

}
